#include "databasehelper.h"

DatabaseHelper::DatabaseHelper(QObject *parent,const QString & name) :
    QObject(parent)
{
    connctionName = name;
    initialDB();
}

DatabaseHelper::~DatabaseHelper()
{
    if(db.isOpen())
        db.close();
}

void DatabaseHelper::addRow(SensorType type, float value, QDateTime dt)
{
    QString sql = "INSERT INTO ";
    switch (type) {
    case TEMPERATURE:
        sql.append("temperature");
        break;
    case HUMIDITY:
        sql.append("humidity");
        break;
    case LIGHT:
        sql.append("light");
        break;
    case PRESSURE:
        sql.append("pressure");
        break;
    case HEIGHT:
        sql.append("height");
        break;
    case VOLTAGE:
        sql.append("voltage");
        break;
    case X:
        sql.append("gyro_x");
        break;
    case Y:
        sql.append("gyro_y");
        break;
    case Z:
        sql.append("gyro_z");
        break;
    case COMPASS:
        sql.append("compass");
        break;
    case LED:
        sql.append("led1");
        break;
    case BUZZER:
        sql.append("buzzer");
        break;
    }
    sql.append(" (val,stamp) VALUES(?,?)");

    QSqlQuery sq;
    sq.prepare(sql);
    sq.addBindValue(value);
    sq.addBindValue(dt.currentMSecsSinceEpoch()/1000);
    if(sq.exec())
    {
//        qDebug() << "insert success!";
    }else
    {
        qDebug() << "insert failed!" << sq.lastError().text();
    }
}

QList<QString> DatabaseHelper::selectData(SensorType type,qint64 start, qint64 end)
{
    QString sql = "SELECT * FROM ";
    QList<QString> result;

    switch (type) {
    case TEMPERATURE:
        sql.append("temperature");
        break;
    case HUMIDITY:
        sql.append("humidity");
        break;
    case LIGHT:
        sql.append("light");
        break;
    case PRESSURE:
        sql.append("pressure");
        break;
    case HEIGHT:
        sql.append("height");
        break;
    case VOLTAGE:
        sql.append("voltage");
        break;
    case X:
        sql.append("gyro_x");
        break;
    case Y:
        sql.append("gyro_y");
        break;
    case Z:
        sql.append("gyro_z");
        break;
    case COMPASS:
        sql.append("compass");
        break;
    case LED:
        sql.append("led1");
        break;
    case BUZZER:
        sql.append("buzzer");
        break;
    }
    QSqlQuery sq;
    sql.append(" WHERE stamp BETWEEN ? AND ?");
    sq.prepare(sql);
    sq.addBindValue(start);
    sq.addBindValue(end);
    if(sq.exec())
    {
        isSelecting = true;
        while(isSelecting && sq.next())
        {
            QString record = sq.value(0).toString();
            record.append(" ");
            record.append(sq.value(1).toString());
            record.append(" ");
            record.append(sq.value(2).toString());
            result.append(record);
            if(result.size() == 1000) // 最大保存数据量1000条
                break;
        }
        isSelecting = false;
        qDebug() << "select success!";
    }else
    {
        qDebug() << "select failed!" << sq.lastError().text();
    }
    return result;
}

QString DatabaseHelper::getConnctionName() const
{
    return connctionName;
}

void DatabaseHelper::setConnctionName(const QString &value)
{
    connctionName = value;
}
bool DatabaseHelper::getIsSelecting() const
{
    return isSelecting;
}

void DatabaseHelper::setIsSelecting(bool value)
{
    isSelecting = value;
}

void DatabaseHelper::initialDB()
{
    if(connctionName == "")
        db = QSqlDatabase::addDatabase("QSQLITE");
    else
        db = QSqlDatabase::addDatabase("QSQLITE",connctionName);
    db.setDatabaseName("sensor_data.db");
    if(db.open())
    {
        QSqlQuery sq;
        QString sql = "CREATE TABLE temperature(id INTEGER PRIMARY KEY,val REAL,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE humidity(id INTEGER PRIMARY KEY,val REAL,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE light(id INTEGER PRIMARY KEY,val REAL,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE pressure(id INTEGER PRIMARY KEY,val INTEGER,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE height(id INTEGER PRIMARY KEY,val INTEGER,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE voltage(id INTEGER PRIMARY KEY,val REAL,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE gyro_x(id INTEGER PRIMARY KEY,val REAL,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE gyro_y(id INTEGER PRIMARY KEY,val REAL,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE gyro_z(id INTEGER PRIMARY KEY,val REAL,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE compass(id INTEGER PRIMARY KEY,val REAL,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE led1(id INTEGER PRIMARY KEY,val INTEGER,stamp INTEGER)";
        sq.exec(sql);
        sql = "CREATE TABLE buzzer(id INTEGER PRIMARY KEY,val INTEGER,stamp INTEGER)";
        sq.exec(sql);
    }else
    {
        qDebug() << "db open failed!" << db.lastError().text();
    }

}
